package com.dailyblue.java.jdbc.pack0325.dao;

import com.dailyblue.java.jdbc.pack0325.bean.Dept;
import com.dailyblue.java.jdbc.pack0325.bean.Emp;
import com.dailyblue.java.jdbc.pack0325.db.DBManager;
import lombok.SneakyThrows;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

// bean+DAO-->Data Access Object
public class EmpDAO {

    private Connection conn;
    private PreparedStatement ps;
    private ResultSet rs;

    public List<Emp> findAllEmp() {
        List<Emp> list = new ArrayList<>();
        try {
            conn = DBManager.getConnection();
            String sql = "select * from emp where state = 1";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                // 目前 rs 代表当前行
                int empNo = rs.getInt(1);
                String ename = rs.getString(2);
                String job = rs.getString("job");
                Integer mgr = rs.getInt("mgr");
                String hireDate = rs.getString("hireDate");
                Double sal = rs.getDouble(6);
                Double comm = rs.getDouble(7);
                Integer deptNo = rs.getInt(8);
                Emp emp = new Emp(empNo, ename, job, mgr, hireDate, sal, comm, deptNo, 1);
                list.add(emp);
            }
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DBManager.closeConnection(conn, ps, rs);
        }
        return list;
    }

    public List<Emp> findEmpByEname(String ename) {
        List<Emp> list = new ArrayList<>();
        try {
            conn = DBManager.getConnection();
            String sql = "select * from emp where state = 1 and ename like ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, "%" + ename + "%");
            rs = ps.executeQuery();
            while (rs.next()) {
                // 目前 rs 代表当前行
                int empNo = rs.getInt(1);
                String ename1 = rs.getString(2);
                String job = rs.getString("job");
                Integer mgr = rs.getInt("mgr");
                String hireDate = rs.getString("hireDate");
                Double sal = rs.getDouble(6);
                Double comm = rs.getDouble(7);
                Integer deptNo = rs.getInt(8);
                Emp emp = new Emp(empNo, ename1, job, mgr, hireDate, sal, comm, deptNo, 1);
                list.add(emp);
            }
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DBManager.closeConnection(conn, ps, rs);
        }
        return list;
    }

    public void updateEmp(Emp emp) {
        try {
            conn = DBManager.getConnection();
            String sql = "update emp set ename=?,job=?,mgr=?,sal=?,comm=?,deptNo=? where empNo=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, emp.getEname());
            ps.setString(2, emp.getJob());
            ps.setInt(3, emp.getMgr());
            ps.setDouble(4, emp.getSal());
            ps.setDouble(5, emp.getComm());
            ps.setInt(6, emp.getDeptNo());
            ps.setInt(7, emp.getEmpNo());
            ps.executeUpdate();
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBManager.closeConnection(conn, ps, null);
        }

    }

    @SneakyThrows
    public void deleteEmp(int id) {
        conn = DBManager.getConnection();
        String sql = "update emp set state=0 where empno=" + id;
        ps = conn.prepareStatement(sql);
        ps.executeUpdate();
    }

    public List<Emp> findEmp(int page) {
        List<Emp> list = new ArrayList<>();
        try {
            conn = DBManager.getConnection();
            String sql = "select * from emp left join dept on emp.deptno=dept.deptno where state=1 limit ?,5";

            ps = conn.prepareStatement(sql);
            ps.setInt(1, (page - 1) * 5);
            rs = ps.executeQuery();
            while (rs.next()) {
                // 目前 rs 代表当前行
                int empNo = rs.getInt(1);
                String ename = rs.getString(2);
                String job = rs.getString("job");
                Integer mgr = rs.getInt("mgr");
                String hireDate = rs.getString("hireDate");
                Double sal = rs.getDouble(6);
                Double comm = rs.getDouble(7);
                Integer deptNo = rs.getInt(8);
                String dname = rs.getString(11);
                String loc = rs.getString(12);
                Emp emp = new Emp(empNo, ename, job, mgr, hireDate, sal, comm, deptNo, 1);
                Dept dept = new Dept();
                dept.setDeptNo(deptNo);
                dept.setLoc(loc);
                dept.setDname(dname);
                // 组装
                emp.setDept(dept);
                list.add(emp);
            }
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DBManager.closeConnection(conn, ps, rs);
        }
        return list;
    }

    public int getAllPageNum(int size) {
        String sql = "select count(empno) from emp where state=1";
        int pageNum = 0;
        try {
            conn = DBManager.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()) {
                int count = rs.getInt(1);
                pageNum = (count % size == 0) ? (count / size) : (count / size + 1);
            }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return pageNum;
    }

    // 关联写法
    public Emp findEmpByNo(int id) {
        String sql = "select * from emp left join dept on emp.deptno=dept.deptno where state=1 and empno=?";
        Emp emp = null;
        try {
            conn = DBManager.getConnection2();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                // 目前 rs 代表当前行
                int empNo = rs.getInt(1);
                String ename = rs.getString(2);
                String job = rs.getString("job");
                Integer mgr = rs.getInt("mgr");
                String hireDate = rs.getString("hireDate");
                Double sal = rs.getDouble(6);
                Double comm = rs.getDouble(7);
                Integer deptNo = rs.getInt(8);
                String dname = rs.getString(11);
                String loc = rs.getString(12);
                emp = new Emp(empNo, ename, job, mgr, hireDate, sal, comm, deptNo, 1);
                Dept dept = new Dept();
                dept.setDeptNo(deptNo);
                dept.setLoc(loc);
                dept.setDname(dname);
                // 组装
                emp.setDept(dept);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DBManager.closeConnection(conn, ps, rs);
        }
        return emp;
    }

    // 组装写法
    public Emp findEmpByNo2(int id) {
        String sql = "select * from emp where state=1 and empno=?";
        try {
            conn = DBManager.getConnection2();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                int empNo = rs.getInt(1);
                String ename = rs.getString(2);
                String job = rs.getString("job");
                Integer mgr = rs.getInt("mgr");
                String hireDate = rs.getString("hireDate");
                Double sal = rs.getDouble(6);
                Double comm = rs.getDouble(7);
                Integer deptNo = rs.getInt(8);
                Emp emp = new Emp(empNo, ename, job, mgr, hireDate, sal, comm, deptNo, 1);
                return emp;
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DBManager.closeConnection(conn, ps, rs);
        }
        return null;
    }
}
